# -*- coding:utf-8 -*-

from sqlalchemy import select, func, update, delete
from sqlalchemy.ext.asyncio import AsyncSession
from module_admin.entity.do.server_host_do import ServerHost, ServerCommandLog
from typing import Optional, List


class ServerHostDao:
    """服务器主机DAO"""

    @classmethod
    async def get_server_host_by_id(cls, db: AsyncSession, host_id: int) -> Optional[ServerHost]:
        """根据ID获取主机信息"""
        result = await db.execute(
            select(ServerHost).where(
                ServerHost.id == host_id,
                ServerHost.del_flag == '0'
            )
        )
        return result.scalars().first()

    @classmethod
    async def get_server_host_by_ip(cls, db: AsyncSession, host_ip: str) -> Optional[ServerHost]:
        """根据IP获取主机信息"""
        result = await db.execute(
            select(ServerHost).where(
                ServerHost.host_ip == host_ip,
                ServerHost.del_flag == '0'
            )
        )
        return result.scalars().first()
    
    @classmethod
    async def get_server_host_by_name(cls, db: AsyncSession, host_name: str) -> Optional[ServerHost]:
        """根据主机名获取主机信息"""
        result = await db.execute(
            select(ServerHost).where(
                ServerHost.host_name == host_name,
                ServerHost.del_flag == '0'
            )
        )
        return result.scalars().first()
    
    @classmethod
    async def get_hosts_by_name_prefix(cls, db: AsyncSession, name_prefix: str) -> List[ServerHost]:
        """根据主机名前缀获取所有主机（用于获取最大序号）"""
        result = await db.execute(
            select(ServerHost).where(
                ServerHost.host_name.like(f"{name_prefix}-%"),
                ServerHost.del_flag == '0'
            )
        )
        return list(result.scalars().all())

    @classmethod
    async def get_server_host_list(
        cls,
        db: AsyncSession,
        query_dict: dict,
        offset: int = 0,
        limit: int = 10
    ) -> tuple[List[ServerHost], int]:
        """获取主机列表（分页，按主机名自然排序）"""
        import re
        
        # 构建查询条件
        conditions = [ServerHost.del_flag == '0']
        
        if query_dict.get('host_name'):
            conditions.append(ServerHost.host_name.like(f"%{query_dict['host_name']}%"))
        if query_dict.get('host_ip'):
            conditions.append(ServerHost.host_ip.like(f"%{query_dict['host_ip']}%"))
        if query_dict.get('agent_status'):
            conditions.append(ServerHost.agent_status == query_dict['agent_status'])
        if query_dict.get('connection_status'):
            conditions.append(ServerHost.connection_status == query_dict['connection_status'])
        if query_dict.get('status'):
            conditions.append(ServerHost.status == query_dict['status'])

        # 查询总数
        count_result = await db.execute(
            select(func.count(ServerHost.id)).where(*conditions)
        )
        total = count_result.scalar()

        # 查询所有数据（不分页，用于排序）
        result = await db.execute(
            select(ServerHost)
            .where(*conditions)
        )
        all_hosts = result.scalars().all()
        
        # 自然排序函数
        def natural_sort_key(host):
            """生成自然排序的key"""
            host_name = host.host_name or ''
            
            # 如果没有主机名，排到最后
            if not host_name:
                return (1, '', 0)
            
            # 使用正则表达式提取前缀和数字
            parts = re.split(r'(\d+)', host_name)
            
            # 构建排序key：将数字部分转换为整数
            key_parts = []
            for part in parts:
                if part.isdigit():
                    key_parts.append((0, int(part)))  # 数字部分用整数排序
                else:
                    key_parts.append((1, part))  # 字符串部分用字符串排序
            
            return (0, tuple(key_parts))
        
        # 按主机名自然排序
        sorted_hosts = sorted(all_hosts, key=natural_sort_key)
        
        # 分页
        paginated_hosts = sorted_hosts[offset:offset + limit]

        return list(paginated_hosts), total

    @classmethod
    async def create_server_host(cls, db: AsyncSession, host: ServerHost) -> ServerHost:
        """创建主机"""
        db.add(host)
        await db.flush()
        await db.refresh(host)
        return host

    @classmethod
    async def update_server_host(cls, db: AsyncSession, host_id: int, update_dict: dict) -> bool:
        """更新主机信息"""
        await db.execute(
            update(ServerHost)
            .where(ServerHost.id == host_id, ServerHost.del_flag == '0')
            .values(**update_dict)
        )
        return True

    @classmethod
    async def delete_server_host(cls, db: AsyncSession, host_id: int) -> bool:
        """删除主机（软删除）"""
        await db.execute(
            update(ServerHost)
            .where(ServerHost.id == host_id)
            .values(del_flag='2')
        )
        return True

    @classmethod
    async def batch_delete_server_hosts(cls, db: AsyncSession, host_ids: List[int]) -> bool:
        """批量删除主机（软删除）"""
        await db.execute(
            update(ServerHost)
            .where(ServerHost.id.in_(host_ids))
            .values(del_flag='2')
        )
        return True

    @classmethod
    async def get_all_active_hosts(cls, db: AsyncSession) -> List[ServerHost]:
        """获取所有激活的主机"""
        result = await db.execute(
            select(ServerHost).where(
                ServerHost.del_flag == '0',
                ServerHost.status == '0'
            )
        )
        return list(result.scalars().all())

    @classmethod
    async def get_hosts_by_ids(cls, db: AsyncSession, host_ids: List[int]) -> List[ServerHost]:
        """根据ID列表获取主机"""
        result = await db.execute(
            select(ServerHost).where(
                ServerHost.id.in_(host_ids),
                ServerHost.del_flag == '0'
            )
        )
        return list(result.scalars().all())


class ServerCommandLogDao:
    """服务器命令日志DAO"""

    @classmethod
    async def create_command_log(cls, db: AsyncSession, log: ServerCommandLog) -> ServerCommandLog:
        """创建命令日志"""
        db.add(log)
        await db.flush()
        await db.refresh(log)
        return log

    @classmethod
    async def update_command_log(cls, db: AsyncSession, log_id: int, update_dict: dict) -> bool:
        """更新命令日志"""
        await db.execute(
            update(ServerCommandLog)
            .where(ServerCommandLog.id == log_id)
            .values(**update_dict)
        )
        return True

    @classmethod
    async def get_command_logs(
        cls,
        db: AsyncSession,
        host_id: Optional[int] = None,
        command_type: Optional[str] = None,
        offset: int = 0,
        limit: int = 10
    ) -> tuple[List[ServerCommandLog], int]:
        """获取命令日志列表"""
        conditions = []
        
        if host_id:
            conditions.append(ServerCommandLog.host_id == host_id)
        if command_type:
            conditions.append(ServerCommandLog.command_type == command_type)

        # 查询总数
        count_result = await db.execute(
            select(func.count(ServerCommandLog.id)).where(*conditions) if conditions 
            else select(func.count(ServerCommandLog.id))
        )
        total = count_result.scalar()

        # 查询数据列表
        query = select(ServerCommandLog)
        if conditions:
            query = query.where(*conditions)
        query = query.order_by(ServerCommandLog.create_time.desc()).offset(offset).limit(limit)
        
        result = await db.execute(query)
        logs = result.scalars().all()

        return list(logs), total

